/***********************************************************************************************************************************
 * Name:	Server_Logins.sql
 * Author:	Kendal Van Dyke
 *			http://www.kendalvandyke.com/2009/01/scripting-server-permissions-and-role.html
 * Summary:	Generate statements to create server permissions for SQL logins, Windows Logins, and Groups
 */
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON 

SELECT 'USE '+QUOTENAME('master') AS '--Database Context' 

EXEC sp_help_revlogin

-- Role Members 
SELECT
	'EXEC sp_addsrvrolemember @rolename = ' + QUOTENAME(usr1.name, '''') + ', @loginame = ' + QUOTENAME(usr2.name, '''') AS '--Role Memberships' 
  FROM sys.server_principals AS usr1 
	INNER JOIN sys.server_role_members AS rm ON (usr1.principal_id = rm.role_principal_id )
	INNER JOIN sys.server_principals AS usr2 ON (rm.member_principal_id = usr2.principal_id )
  ORDER BY rm.role_principal_id ASC 

-- Permissions 
SELECT
	server_permissions.state_desc				COLLATE SQL_Latin1_General_CP1_CI_AS 
	+ ' ' + server_permissions.permission_name	COLLATE SQL_Latin1_General_CP1_CI_AS 
	+ ' TO [' + server_principals.name			COLLATE SQL_Latin1_General_CP1_CI_AS 
	+ ']' AS '--Server Level Permissions'
  FROM sys.server_permissions AS server_permissions
	INNER JOIN sys.server_principals AS server_principals ON (server_permissions.grantee_principal_id = server_principals.principal_id)
  WHERE server_principals.type IN ('S','U','G') 
  ORDER BY server_principals.name, server_permissions.state_desc, server_permissions.permission_name
GO